package main.niit.dao;

import main.niit.pojo.*;
import main.niit.utils.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class Get6 {
    public List<AdProvinceTop3> getall() throws Exception {
        Connection connection=JDBCUtils.getConnection();
        List<AdProvinceTop3> list6= new ArrayList<>();

        PreparedStatement ps6=null;
        ResultSet resultSet6=null;
        try {
            String sql6 = "SELECT date , province, clickCount  FROM ad_province_top3 group by date,clickCount order by clickCount desc limit 3;";
            ps6 = connection.prepareStatement(sql6);
            resultSet6 = ps6.executeQuery();
            while (resultSet6.next()) {
                AdProvinceTop3 adProvinceTop3= new AdProvinceTop3();
                adProvinceTop3.setDate(resultSet6.getString("date"));
                adProvinceTop3.setProvince(resultSet6.getString("province"));
                adProvinceTop3.setClickCount(resultSet6.getLong("clickCount"));
                list6.add(adProvinceTop3);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            JDBCUtils.close(ps6,connection);
        }


        return list6;

    }
}
